package www.lagou.app;

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.junit.Test;
import www.lagou.entity.Employee;
import www.lagou.entity.Phone;
import www.lagou.utils.DruidUtils;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;
import java.util.Map;

public class AccountTest {

    public void main(String[] args) {

        try {
            task03();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

    }

    @Test
    public void task01() throws SQLException {

        Connection con = DruidUtils.getConnection();

        QueryRunner qr = new QueryRunner();

        String sql = "SELECT balance FROM test01.account WHERE card = '1122334455'";

        Object[] query = qr.query(con,sql, new ArrayHandler());

        double balance = (double) query[0];

        con.setAutoCommit(false);
        qr.update(con, "UPDATE test01.account SET balance = balance - ? WHERE card = '1122334455'", new Object[]{5000});
        qr.update(con, "UPDATE test01.account SET balance = balance + ? WHERE card = '55443332211'", new Object[]{5000});
        qr.update(con, "INSERT INTO test01.transaction(id, cardid, tratype, tramoney, tradate) VALUES (?,?,?,?,?)", new Object[]{null, "1122334455", "转出", 5000, new Date()});
        qr.update(con, "INSERT INTO test01.transaction(id, cardid, tratype, tramoney, tradate) VALUES (?,?,?,?,?)", new Object[]{null, "55443332211", "转入", 5000, new Date()});

        if (balance >= 5000) {
            System.out.println("转账成功");
            DbUtils.commitAndCloseQuietly(con);
        } else {
            System.out.println("余额不足，转账失败。");
            DbUtils.rollbackAndCloseQuietly(con);
        }
        DbUtils.close(con);

    }

    @Test
    public void task02() throws SQLException {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        List<Phone> query1 = queryRunner.query("SELECT * FROM test01.phone WHERE price>2000 AND year(prodate)<2019", new BeanListHandler<Phone>(Phone.class));
        List<Phone> query2 = queryRunner.query("SELECT * FROM test01.phone WHERE color='白色'", new BeanListHandler<Phone>(Phone.class));
        System.out.println("查询价格高于2000元，生产日期是2019年之前的所有手机");
        for (Phone p : query1
        ) {
            System.out.println(p);
        }
        System.out.println("查询所有颜色是白色的手机信息");
        for (Phone p : query2
        ) {
            System.out.println(p);
        }

    }

    @Test
    public void task03() throws SQLException{
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        List<Employee> query1 = queryRunner.query("SELECT * from employee where did is NOT null", new BeanListHandler<>(Employee.class));
        List<Map<String, Object>> query2 = queryRunner.query("SELECT e.name,e.salary,d.deptname from employee e left join dept d on e.did = d.id", new MapListHandler());
        System.out.println("查询所有的员工信息 (不包含没有部门的员工)。");
        for (Employee p : query1
        ) {
            System.out.println(p);
        }
        System.out.println("查询每个员工的 姓名, 薪资 和 所属部门名称");
        for (Map<String, Object> e : query2
        ) {
            System.out.println(e);
        }
    }


}